/****** Object:  Stored Procedure [dbo].spBienBanNhapHangInsert Script Date: 7/16/2012 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spBienBanNhapHangInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spBienBanNhapHangInsert]
GO

/******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:					Description:
**		--------	--------				-----------------------------------
**		17/07/2012	SongTM					Innit created
*******************************************************************************/

CREATE PROCEDURE [dbo].[spBienBanNhapHangInsert]
(
	@MaBBNH nvarchar(12) output,
	@SoHopDong nvarchar(12),
	@NoiNhan nvarchar(50),
	@NgayGioNhan datetime,
	@NgayGioKiem datetime,
	@KetQua nvarchar(200),
	@NguoiDuyet nvarchar(35),
	@KeToan nvarchar(35),
	@ThuKho nvarchar(35)
)
WITH ENCRYPTION

AS

SET NOCOUNT ON

Declare @Stt char(3)
Declare @y varchar(2)
Declare @m varchar(2)

	set @y = RIGHT('0' + YEAR(@NgayGioKiem),2)
	set @m =RIGHT('0' + MONTH(@NgayGioKiem),2)
if not exists (select * from [tbl_BienBanNhapHang]) 
	Set @Stt=1
--else if not exists (select * from [tbl_BienBanNhapHang] where RIGHT(MaBBNH,4) = @m + @y)
--	Set @Stt=1
else
	Set @Stt=(select MAX(RIGHT(LEFT([MaBBNH],6),3)) from [tbl_BienBanNhapHang])+1
	--Set @Stt=(select MAX(RIGHT(LEFT([MaBBNH],6),3)) from [tbl_BienBanNhapHang] where RIGHT(MaBBNH,4) = @m + @y)+1
Set @Stt=REPLICATE('0',3-LEN(@Stt))+@Stt
set @m = REPLICATE('0',2-LEN(@m))+@m
Set @MaBBNH='BBNoooo/mmyy'
Set @MaBBNH=REPLACE(@MaBBNH,'oooo',@Stt);
Set @MaBBNH=REPLACE(@MaBBNH,'mm',@m);
Set @MaBBNH=REPLACE(@MaBBNH,'yy',@y);

INSERT INTO [tbl_BienBanNhapHang]
(
	[MaBBNH],
	[SoHopDong],
	[NoiNhan],
	[NgayGioNhan],
	[NgayGioKiem],
	[KetQua],
	[NguoiDuyet],
	[KeToan],
	[ThuKho]
)
VALUES
(
	@MaBBNH,
	@SoHopDong,
	@NoiNhan,
	@NgayGioNhan,
	@NgayGioKiem,
	@KetQua,
	@NguoiDuyet,
	@KeToan,
	@ThuKho
)
GO

--GRANT EXECUTE ON [dbo].[spBienBanNhapHangInsert] TO [sa]
--GO
